﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Web;
using System.Web.Services;
using System.Xml.Serialization;
using Classes;

namespace IceBirdServer
{
	/// <summary>
	/// SmallBoneService 的摘要说明
	/// </summary>
	[WebService(Namespace = "http://tempuri.org/")]
	[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
	[System.ComponentModel.ToolboxItem(false)]
	

	// 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。
	// [System.Web.Script.Services.ScriptService]
	[XmlInclude(typeof(IceBird.MessageSet))]
	[XmlInclude(typeof(IceBird.TopicSet))]
	[XmlInclude(typeof(IceBird.GroupSet))]
	[XmlInclude(typeof(IceBird.username))]
	[XmlInclude(typeof(IceBird.userid))]
	[XmlInclude(typeof(IceBird.SortForm))]
	[XmlInclude(typeof(IceBird.LoginState))]
	public class SmallBoneService : System.Web.Services.WebService
	{

		private const string strConnection
			= "integrated security = true;"
			+ "Server = localhost;"
			+ "DataBase = SmallBones";

		private const int NoSuchIdErrorCode = -2146232060;

		private SqlConnection SqlConnect()
		{
			SqlConnection objConnection = new SqlConnection(strConnection);
			objConnection.Open();
			return objConnection;
		}

		private SqlDataReader SqlOption(SqlConnection connection, string curOP)
		{
			//db.Form1.debugText += curOP + "\n";
			return (new SqlCommand(curOP, connection)).ExecuteReader();
		}

		[WebMethod]
		public string Init()
		{
			return "Welcome!!";
		}

		/// <summary>
		/// 注册新账户操作
		/// </summary>
		/// <param name="UserName">用户名</param>
		/// <param name="PassWord">密码</param>
		/// <returns></returns>
		[WebMethod]
		[XmlInclude(typeof(AssignResult))]
		//[XmlInclude(typeof(Classes.AssignResult))]
		public AssignResult DBSignIn(string UserName, string PassWord)
		{
			UserName = UserName.Trim();
			if (UserName == "")
				return new AssignResult(IceBird.AssignState.NAME_EMPTY);
			if (UserName.Length > IceBird.LengthLimit.maxNameLength)
				return new AssignResult(IceBird.AssignState.NAME_TOO_LONG);
			if (PassWord.Length > IceBird.LengthLimit.maxPwdLength)
				return new AssignResult(IceBird.AssignState.PWD_TOO_LONG);
			for (int i = 0; i < UserName.Length; i++)
				if (UserName[i] <= ' ' || UserName[i] > 127)
					return new AssignResult(IceBird.AssignState.NAME_ERROR_CHAR);
			for (int i = 0; i < PassWord.Length; i++)
				if (PassWord[i] < ' ')
					return new AssignResult(IceBird.AssignState.PWD_ERROR_CHAR);
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select [name] from [dbo].[User] where [name] = '" + UserName + "'");
				if (reader.Read())
					return new AssignResult(IceBird.AssignState.NAME_USED);
				reader.Close();
				reader = SqlOption(objConnection,
					"INSERT INTO [dbo].[User] ([name] , [password]) VALUES ('" + UserName + "','" + PassWord + "')");
				reader.Close();
				reader = SqlOption(objConnection,
					"select [id] from [dbo].[User] where [name] = '" + UserName + "'");
				reader.Read();
				return new AssignResult((int)reader[0]);
			}
			catch
			{
				return new AssignResult(IceBird.AssignState.ERROR_WHILE_READ_DB);
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		[WebMethod]
		public IceBird.LoginState LogIn(string UserName, string PassWord, string ClientIPAddress, DateTime time)
		{//登录并返回账号UserId
			try
			{
				SqlConnection Connection = new SqlConnection(strConnection);
				Connection.Open();
				SqlCommand cmd = new SqlCommand("select id,password from [SmallBones].dbo.[User] where name = '" + UserName + "'", Connection);
				SqlDataReader reader = cmd.ExecuteReader();
				IceBird.LoginState Result = IceBird.LoginState.USERNOTEXIST;
				int id = 0;
				if (reader.Read())
				{
					if (PassWord == (string)reader[1])
					{
						Result = IceBird.LoginState.LOGIN;
						id = (int)reader[0];
					}
					else
						Result = IceBird.LoginState.WRONGPASSWORD;
				}
				reader.Close();

				if (Result == IceBird.LoginState.LOGIN)
				{
					SqlCommand Log = new SqlCommand("insert into [SmallBones].dbo.[Login] (userid,inout,[time],ip) values (" + id.ToString() + ",1,'" + time.ToString() + "','" + ClientIPAddress + "')", Connection);
					Log.ExecuteNonQuery();
				}
				Connection.Close();
				return Result;
			}
			catch
			{
				return IceBird.LoginState.DBERROR;
			}
		}

		/// <summary>
		/// 获取编号在一个区间内的Group
		/// </summary>
		/// <param name="StartPosition">区间下界</param>
		/// <param name="EndPosition">区间上界，注意是左闭右开区间</param>
		/// <param name="sortForm">排序依据</param>
		/// <returns>若返回值的err成员为OK，则其他成员一定会被赋值，其他情况请参照DBError，未说明则不会赋值</returns>
		[WebMethod]
		[XmlInclude(typeof(IceBird.GroupSet))]
		public IceBird.GroupSet GetGroups(int StartPosition, int EndPosition, IceBird.SortForm sortForm = IceBird.SortForm.DEFAULT)
		{//进入Groups界面，得到从StartPosition到EndPosition部分的Groups列表
			IceBird.GroupSet groupSet = new IceBird.GroupSet();
			if (StartPosition > EndPosition)
			{
				groupSet.err = IceBird.DBError.LOWER_BOUND_LARGER_THAN_HIGHER_BOUND;
				return groupSet;
			}
			if (StartPosition + IceBird.LengthLimit.maxCountPerQuery < EndPosition)
			{
				groupSet.err = IceBird.DBError.SCOPE_TOO_LARGE;
				EndPosition = StartPosition + IceBird.LengthLimit.maxCountPerQuery;
			}
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select max([id]) from [dbo].[Group]");
				if (reader.Read())
					groupSet.groupCount = (int)reader[0];
				else
					groupSet.groupCount = 0;
				reader.Close();
				if (sortForm == IceBird.SortForm.LAST)
				{
					int tmp = EndPosition;
					EndPosition = groupSet.groupCount - StartPosition + 2;
					StartPosition = groupSet.groupCount - tmp + 2;
				}
				reader = SqlOption(objConnection,
					"select * from [dbo].[Group] where [id] between " + StartPosition + " and " + (EndPosition - 1));
				List<IceBird.Group> groups = new List<IceBird.Group>();
				while (reader.Read())
					groups.Add((new IceBird.Group(reader)));
				groupSet.Groups = groups;
				return groupSet;
			}
			catch
			{
				groupSet.err = IceBird.DBError.DB_OPTION_ERROR;
				return groupSet;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		/// <summary>
		/// 获取一个Group下，在该Group下编号在一个区间内的Topic
		/// </summary>
		/// <param name="StartPosition">区间下界</param>
		/// <param name="EndPosition">区间上界，注意是左闭右开区间</</param>
		/// <param name="GroupId">群组编号</param>
		/// <param name="sortForm">排序依据</param>
		/// <returns>若返回值的err成员为OK，则其他成员一定会被赋值，其他情况请参照DBError，未说明则不会赋值</returns>
		[WebMethod]
		public IceBird.TopicSet GetTopics(int StartPosition, int EndPosition, int GroupId, IceBird.SortForm sortForm = IceBird.SortForm.DEFAULT)
		{//进入Topics界面，得到从SP到EP部分的Topics列表
			IceBird.TopicSet topicSet = new IceBird.TopicSet();
			if (StartPosition > EndPosition)
			{
				topicSet.err = IceBird.DBError.LOWER_BOUND_LARGER_THAN_HIGHER_BOUND;
				return topicSet;
			}
			if (StartPosition + IceBird.LengthLimit.maxCountPerQuery < EndPosition)
			{
				topicSet.err = IceBird.DBError.SCOPE_TOO_LARGE;
				EndPosition = StartPosition + IceBird.LengthLimit.maxCountPerQuery;
			}
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select * from [dbo].[Group] where [id] = " + GroupId);
				if (!reader.Read())
				{
					topicSet.err = IceBird.DBError.NO_SUCH_ID;
					return topicSet;
				}
				topicSet.group = new IceBird.Group(reader);
				reader.Close();
				if (sortForm == IceBird.SortForm.LAST)
				{
					int tmp = EndPosition;
					EndPosition = topicSet.group.topicCnt - StartPosition + 2;
					StartPosition = topicSet.group.topicCnt - tmp + 2;
				}
				reader = SqlOption(objConnection,
					"select * from [dbo].[Topic] where ([groupid] = " + GroupId + " ) and ([idingroup] between " + StartPosition + "and " + (EndPosition - 1) + ")");
				List<IceBird.Topic> topics = new List<IceBird.Topic>();
				while (reader.Read())
					topics.Add(new IceBird.Topic(reader));
				topicSet.Topics = topics;
				return topicSet;
			}
			catch
			{
				topicSet.err = IceBird.DBError.DB_OPTION_ERROR;
				return topicSet;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		/// <summary>
		/// 获取一个Topic下，在该Topic下编号在一个区间内的Message
		/// </summary>
		/// <param name="StartPosition">区间下界</param>
		/// <param name="EndPosition">区间上界，注意是左闭右开区间</param>
		/// <param name="TopicId">Topic编号</param>
		/// <param name="sortForm">排序依据</param>
		/// <returns>若返回值的err成员为OK，则其他成员一定会被赋值，其他情况请参照DBError，未说明则不会赋值</returns>
		[WebMethod]
		public IceBird.MessageSet GetReplies(int StartPosition, int EndPosition, int TopicId, IceBird.SortForm sortForm = IceBird.SortForm.DEFAULT)
		{
			IceBird.MessageSet messageSet = new IceBird.MessageSet();
			if (StartPosition > EndPosition)
			{
				messageSet.err = IceBird.DBError.LOWER_BOUND_LARGER_THAN_HIGHER_BOUND;
				return messageSet;
			}
			if (StartPosition + IceBird.LengthLimit.maxCountPerQuery < EndPosition)
			{
				messageSet.err = IceBird.DBError.SCOPE_TOO_LARGE;
				EndPosition = StartPosition + IceBird.LengthLimit.maxCountPerQuery;
			}
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select * from [dbo].[Topic] where [id] = " + TopicId);
				if (!reader.Read())
				{
					messageSet.err = IceBird.DBError.NO_SUCH_ID;
					return messageSet;
				}
				messageSet.topic = new IceBird.Topic(reader);
				reader.Close();
				if (sortForm == IceBird.SortForm.LAST)
				{
					int tmp = EndPosition;
					EndPosition = messageSet.topic.replyCnt - StartPosition + 2;
					StartPosition = messageSet.topic.replyCnt - tmp + 2;
				}
				reader = SqlOption(objConnection,
					"select * from [dbo].[Reply] where ([topicid] = " + TopicId + " ) and ([idintopic] between " + StartPosition + " and " + (EndPosition - 1) + ")");
				List<IceBird.Message> Replies = new List<IceBird.Message>();
				while (reader.Read())
					Replies.Add(new IceBird.Message(reader));
				messageSet.Messages = Replies;
				return messageSet;
			}
			catch
			{
				messageSet.err = IceBird.DBError.DB_OPTION_ERROR;
				return messageSet;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		private bool isEmpty(string st)
		{
			for (int i = 0; i < st.Length; i++)
				if(st[i] < 0 || st[i] > 32)
					return false;
			return true;
		}

		/// <summary>
		/// 新建一个Message
		/// </summary>
		/// <param name="UserId">用户id，用户id不存在createState会被赋值为ERROR_WHILE_READ_DB</param>
		/// <param name="Content">回复的文本，如果只含有空白符，则createState会被赋值为CONTENT_EMPTY</param>
		/// <param name="TopicId">回复的Topic的绝对编号（即Topic类下的id而非idInGroup和NewTopic的返回值）</param>
		/// <returns>若createState成员为Pass，则id为传入的Topic下该Message的编号，否则id为空</returns>
		[WebMethod]
		[XmlInclude(typeof(CreateResult))]
		public CreateResult CommitReply(int UserId, string Content, int TopicId)
		{
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			if (Content.Length > IceBird.LengthLimit.maxMessageLength)
				return new CreateResult(IceBird.CreateState.TOO_LONG);
			if (isEmpty(Content))
				return new CreateResult(IceBird.CreateState.CONTENT_EMPTY);
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select * from [dbo].[topic] where([id] = " + TopicId + ")");
				if (!reader.Read())
					return new CreateResult(IceBird.CreateState.NO_SUCH_ID);
				IceBird.Topic topic = new IceBird.Topic(reader);
				reader.Close();
				reader = SqlOption(objConnection,
					"update [dbo].[topic] set [replycnt] = " + (topic.replyCnt + 1) + " where [id] = " + topic.id);
				reader.Close();
				reader = SqlOption(objConnection,
					"insert [dbo].[reply] ([userid],[time],[topicid],[content],[idintopic]) values " +
					"(" + UserId + ", '" + DateTime.Now.ToString() + "', " + TopicId + ", '" + Content + "', " + (topic.replyCnt + 1) + ")");
				reader.Close();
				return new CreateResult(topic.replyCnt + 1);
			}
			catch (System.Data.SqlClient.SqlException e)
			{
				if (e.ErrorCode == NoSuchIdErrorCode)
					return new CreateResult(IceBird.CreateState.NO_SUCH_ID);
				return new CreateResult(IceBird.CreateState.ERROR_WHILE_READ_DB);
			}
			catch
			{
				return new CreateResult(IceBird.CreateState.ERROR_WHILE_READ_DB);
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		/// <summary>
		/// 新建一个Topic
		/// </summary>
		/// <param name="UserId">用户id，用户id不存在createState会被赋值为ERROR_WHILE_READ_DB</param>
		/// <param name="Content">Topic的名称</param>
		/// <param name="GroupId">所在的Group的编号</param>
		/// <returns>若createState成员为Pass，则id为传入的Group下该Topic的编号，否则id为空</returns>
		[WebMethod]
		[XmlInclude(typeof(CreateResult))]
		public CreateResult NewTopic(int UserId, string TopicName, int GroupId)
		{
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			if (TopicName.Length > IceBird.LengthLimit.maxTopicLength)
				return new CreateResult(IceBird.CreateState.TOO_LONG);
			if (isEmpty(TopicName))
				return new CreateResult(IceBird.CreateState.CONTENT_EMPTY);
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"select * from [dbo].[group] where [id] = " + GroupId);
				if (!reader.Read())
					return new CreateResult(IceBird.CreateState.NO_SUCH_ID);
				IceBird.Group group = new IceBird.Group(reader);
				reader.Close();
				reader = SqlOption(objConnection,
					"update [dbo].[group] set [topiccnt] = " + (group.topicCnt + 1) + " where [id] = " + group.id);
				reader.Close();
				reader = SqlOption(objConnection,
					"insert [dbo].[topic] ([userid],[time],[groupid],[title],[idingroup]) values " +
					"(" + UserId + ", '" + DateTime.Now.ToString() + "', " + GroupId + ", '" + TopicName + "', " + (group.topicCnt + 1) + ")");
				reader.Close();
				return new CreateResult(group.topicCnt + 1);
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				if(e.ErrorCode == NoSuchIdErrorCode)
					return new CreateResult(IceBird.CreateState.NO_SUCH_ID);
				return new CreateResult(IceBird.CreateState.ERROR_WHILE_READ_DB);
			}
			catch{
				return new CreateResult(IceBird.CreateState.ERROR_WHILE_READ_DB);
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		/// <summary>
		/// 新建一个Group
		/// </summary>
		/// <param name="GroupName">Group的名称</param>
		/// <returns>若createState成员为Pass，则id为该Group的编号，否则id为空</returns>
		[WebMethod]
		[XmlInclude(typeof(CreateResult))]
		public CreateResult NewGroup(string GroupName)
		{
			if (GroupName.Length > IceBird.LengthLimit.maxGroupLength)
				return new CreateResult(IceBird.CreateState.TOO_LONG);
			SqlConnection objConnection = null;
			SqlDataReader reader = null;
			try
			{
				objConnection = SqlConnect();
				reader = SqlOption(objConnection,
					"insert [dbo].[group] ([name]) values " + "('" + GroupName + "')");
				reader.Close();
				reader = SqlOption(objConnection,
					"select [id] from [dbo].[group] where [name] = '" + GroupName + "'");
				reader.Read();
				int id = (int)reader[0];
				reader.Close();
				return new CreateResult(id);
			}
			catch
			{
				return new CreateResult(IceBird.CreateState.NO_SUCH_ID);
				//return new CreateResult(IceBird.CreateState.ERROR_WHILE_READ_DB);
			}
			finally
			{
				if (reader != null)
					reader.Close();
				if (objConnection != null)
					objConnection.Close();
			}
		}

		[WebMethod]
		public bool LogOut(string UserName, string ClientIPAddress, DateTime time)
		{//用户登出记录
			try
			{
				SqlConnection Connection = new SqlConnection(strConnection);
				Connection.Open();
				SqlCommand cmd = new SqlCommand("select id from [SmallBones].dbo.[User] where name ='" + UserName + "'", Connection);
				SqlDataReader reader = cmd.ExecuteReader();
				int id = 0;
				if (reader.Read())
				{
					id = (int)reader[0];
				}
				reader.Close();

				if (id != 0)
				{
					SqlCommand Log = new SqlCommand("insert into [SmallBones].dbo.[Login] (userid,inout,[time],ip) values (" + id.ToString() + ",0,'" + time.ToString() + "','" + ClientIPAddress + "')", Connection);
					Log.ExecuteNonQuery();
					Connection.Close();
					return true;
				}
				Connection.Close();
				return false;
			}
			catch
			{
				return false;
			}
		}

		[WebMethod]
		public IceBird.username UseridToUsername(int Userid)
		{
			IceBird.username name = new IceBird.username();
			SqlConnection con = new SqlConnection(strConnection);
			try
			{
				con.Open();
				string Command = "select name from[SmallBones].dbo.[User] where id =" + Userid.ToString();
				SqlCommand cmd = new SqlCommand(Command, con);
				SqlDataReader reader = cmd.ExecuteReader();
				if (reader.Read())
				{
					name.Username = (string)reader[0];
					name.Exist = true;
				}
				con.Close();
				return name;
			}
			catch
			{
				name.DbError = true;
				con.Close();
				return name;
			}
		}

		[WebMethod]
		public IceBird.userid UsernameToUserid(string username)
		{
			IceBird.userid id = new IceBird.userid();
			SqlConnection con = new SqlConnection(strConnection);
			try
			{
				con.Open();
				SqlCommand cmd = new SqlCommand("select id,name from [SmallBones].dbo.[User]", con);
				SqlDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					if (username == (string)reader[1])
					{
						id.Userid = (int)reader[0];
						id.Exist = true;
						con.Close(); reader.Close();
						return id;
					}
				}
				reader.Close();
				con.Close();
				return id;
			}
			catch
			{
				id.Exist = false;
				con.Close();
				return id;
			}
		}
	}

}
